<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
require_once (APPPATH.'libraries/productobject.php');


/**
 * Product Model
 */
class Product_Model extends CI_Model {
	private static $instance;
	function __construct()
	{
		parent::__construct();
		self::$instance =& $this;
	}
	
	public static function getInstance()
	{
		return self::$instance;
	}
	
	public function countProductsByCategoryId($categoryId)
	{
		$query = "SELECT COUNT(DISTINCT p.id) as total "
		."FROM ".PRODUCTS_TABLE." p, ".PRODUCT_TYPE_TABLE." pt, ".CATEGORIES_TABLE." c "
		."WHERE p.product_type_id = pt.id AND pt.category_id = c.id AND c.id=?";
		
		$data = $this->db->query($query, array($categoryId));
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			return $row->total;
		}
		
		return 0;
	}
	
	
	public function getRandomProducts($limit, $productTypeId = -1)
	{
		$offset = 0;//TODO:get random
		$products = array();
		$params = array();
		
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion "
		."FROM ".PRODUCTS_TABLE." p ";
		
		if ($productTypeId > 0)
		{
			$query .= "WHERE p.product_type_id = ? ";
			$params[] = $productTypeId;
		}
		
		$query .= "ORDER BY RAND()"." LIMIT ?,?";
		$params[] = $offset;
		$params[] = $limit;
		
		$data = $this->db->query($query, $params);
		
		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function getRandomPromotionProducts($limit)
	{
		$offset = 0;//TODO:get random
		$products = array();
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion "
		."FROM ".PRODUCTS_TABLE." p WHERE promotion=1 ORDER BY RAND()"
		." LIMIT ?,?";
		
		$data = $this->db->query($query, array($offset, $limit));
		
		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function getRandomBestSellerProducts($limit)
	{
		$offset = 0;//TODO:get random
		$products = array();
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion "
		."FROM ".PRODUCTS_TABLE." p, ".PRODUCT_MARK_TABLE." pm WHERE p.id = pm.product_id 
		AND pm.mark_id in (SELECT m.id As list FROM ".SPECIAL_MARK_TABLE." m WHERE
		m.showall=1) ORDER BY RAND()"
		." LIMIT ?,?";
		
		$data = $this->db->query($query, array($offset, $limit));

		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function getProductsByCategoryId($categoryId, $offset, $limit)
	{
		$products = array();
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion "
		."FROM ".PRODUCTS_TABLE." p, ".PRODUCT_TYPE_TABLE." pt, ".CATEGORIES_TABLE." c "
		."WHERE p.product_type_id = pt.id AND pt.category_id = c.id AND c.id=? LIMIT ?,?";
		
		$data = $this->db->query($query, array($categoryId, $offset, $limit));
		
		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function getProductsByName($name, $offset, $limit)
	{
		$products = array();
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion "
		."FROM ".PRODUCTS_TABLE." p, ".PRODUCT_TYPE_TABLE." pt, ".CATEGORIES_TABLE." c "
		."WHERE p.product_type_id = pt.id AND pt.category_id = c.id AND p.name LIKE ? LIMIT ?,?";
		
		$data = $this->db->query($query, array($this->db->escape_like_str($name), $offset, $limit));
		
		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function countProductsByName($name)
	{
		$total = 0;
		$query = "SELECT COUNT(DISTINCT id) as total FROM ".PRODUCTS_TABLE." WHERE `name` LIKE ?";
		
		$data = $this->db->query($query, array($this->db->escape_like_str($name)));
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			$total = $row->total;
		}
		
		return $total;
	}
	
	public function countProductsByProductTypeId($productTypeId, $equal = TRUE)
	{
		$query = "SELECT COUNT(DISTINCT id) as total FROM ".PRODUCTS_TABLE;
		
		if ($equal)
			$query .= " WHERE product_type_id=?";
		else
			$query .= " WHERE product_type_id != ?";
		
		$data = $this->db->query($query, array($productTypeId));
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			return $row->total;
		}
		
		return 0;
	}
	
	public function getProductsByProductTypeId($productTypeId, $offset, $limit, $equal = TRUE)
	{
		$products = array();
		$query = "SELECT DISTINCT id, product_type_id, branch_id, description, price, image, name, promotion"
		." FROM ".PRODUCTS_TABLE;
		
		if ($equal)
			$query .= " WHERE product_type_id = ? LIMIT ?,?";
		else
			$query .= " WHERE product_type_id != ? LIMIT ?,?";
		
		$data = $this->db->query($query, array($productTypeId, $offset, $limit));
		
		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	
	
	public function getProductsByBranchId($branchId)
	{
		$products = array();
		
		$this->db->where('branch_id', $productTypeId);
		$data = $this->db->get(PRODUCTS_TABLE);
		
		foreach ($data->result() as $row)
		{
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function getProductById($id)
	{
		$product = new ProductObject();
		
		$this->db->where('id', $id);
		$data = $this->db->get(PRODUCTS_TABLE);
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			
			$product->setId($row->id);
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$product->setProductType($productType);
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$product->setMarks($marks);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$product->setBranch($branch);
			$product->setDescription($row->description);
			$product->setPrice($row->price);
			$product->setImage($row->image);
			$product->setName($row->name);
			$product->setPromotion($row->promotion);
		}
		
		return $product;
	}
	
	public function countProductsBySpecialMarkId($id)
	{
		$total = 0;
		$query = "SELECT COUNT(DISTINCT p.id) as total ";
		$query .= "FROM ".PRODUCTS_TABLE." p, ".PRODUCT_MARK_TABLE." pm ";
		$query .= "WHERE p.id = pm.product_id AND pm.mark_id=?";
		
		$data = $this->db->query($query, array($id));
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			$total = $row->total;
		}
		
		return $total;
	}
	
	public function getProductsBySpecialMarkId($id, $offset, $limit)
	{
		$products = array();
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion ";
		$query .= "FROM ".PRODUCTS_TABLE." p, ".PRODUCT_MARK_TABLE." pm ";
		$query .= "WHERE p.id = pm.product_id AND pm.mark_id=? LIMIT ?,?";
		
		$data = $this->db->query($query, array($id, $offset, $limit));
		
		foreach ($data->result() as $row)
		{
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}
	
	public function countProductsNotHaveMark($id)
	{
		$total = 0;
		$query = "SELECT COUNT(DISTINCT p.id) as total ";
		$query .= "FROM ".PRODUCTS_TABLE." p, ".PRODUCT_MARK_TABLE." pm ";
		$query .= "WHERE p.id = pm.product_id AND pm.product_id not in (SELECT pm2.product_id
					FROM product_mark pm2 
					WHERE pm2.mark_id = ?
					GROUP BY pm2.product_id)";
		
		$data = $this->db->query($query, array($id));
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			$total = $row->total;
		}
		
		return $total;
	}
	
	public function getProductsNotHaveMark($id, $offset, $limit)
	{
		$products = array();
		$query = "SELECT DISTINCT p.id, p.product_type_id, p.branch_id, p.description, p.price, p.image, p.name, p.promotion ";
		$query .= "FROM ".PRODUCTS_TABLE." p, ".PRODUCT_MARK_TABLE." pm ";
		$query .= "WHERE p.id = pm.product_id AND pm.product_id not in (SELECT pm2.product_id
					FROM product_mark pm2 
					WHERE pm2.mark_id = ?
					GROUP BY pm2.product_id) LIMIT ?,?";
		
		$data = $this->db->query($query, array($id, $offset, $limit));
		
		foreach ($data->result() as $row)
		{
			$productType = $this->producttype_model->getProductTypeById($row->product_type_id);
			$branch = $this->branch_model->getBranchById($row->branch_id);
			$marks = $this->specialmark_model->getSpecialMarksByProductId($row->id);
			$products[] = new ProductObject($row->id, $productType, $branch, $row->description, $row->price, $marks, $row->image, $row->name, $row->promotion);
		}
		
		return $products;
	}

	public function deleteProductById($id)
	{
		$this->db->trans_start();
		$this->db->where('id', $id);
		$this->db->delete(PRODUCTS_TABLE);
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE)
			return DELETE_ERROR;
		
		return ERROR_NONE;
	}
	
	public function deleteMultipleProducts($ids)
	{
		$query = "";
		$size = count($ids);
		$query = "DELETE FROM ".PRODUCTS_TABLE." WHERE id IN (";
		
		for($i = 0; $i < ($size - 1); $i++)
		{
			$query .= "?, ";
		}
		
		$query .= "?)";
		
		$this->db->trans_start();
		$this->db->query($query, $ids);
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE)
			return DELETE_ERROR;
		return ERROR_NONE;
	}
	
	public function insertProduct($product)
	{
		$query = 'INSERT INTO '.PRODUCTS_TABLE.' VALUES(NULL, ?, ?, ?, ?, ?, ?, ?)';
		
		$this->db->trans_start();
		$this->db->query($query, array($product->getProductType()->getId(), $product->getBranch()->getId(), $product->getDescription(), $product->getPrice(), $product->getImage(), $product->getName(), $product->isPromotion()));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE)
			return INSERT_ERROR;
		
		// Insert values into PRODUCT_MARK table
		$lastid = $this->db->query('SELECT LAST_INSERT_ID()');
		$row = $lastid->row_array();
		$pid = $row['LAST_INSERT_ID()'];
		
		foreach ($product->getMarks() as $mark)
		{
			if ($mark->getId() > 0)
			{
				$data = array(	'product_id'	=> $pid, 
							'mark_id'		=> $mark->getId());
							
				$this->db->trans_start();
				$this->db->insert(PRODUCT_MARK_TABLE, $data);
				$this->db->trans_complete();
				
				if ($this->db->trans_status() === FALSE)
					return INSERT_ERROR;
			}
		}
		
		return ERROR_NONE;
	}
	
	public function updateProduct($id, $product)
	{
		$query = 'UPDATE '.PRODUCTS_TABLE.' SET product_type_id=?, branch_id=?, description=?, price=?, image=?, name=?, promotion=? WHERE id=?';
		
		$this->db->trans_start();
		$this->db->query($query, array($product->getProductType()->getId(), $product->getBranch()->getId(), $product->getDescription(), $product->getPrice(), $product->getImage(), $product->getName(), $product->isPromotion(), $id));
		
		foreach ($product->getMarks() as $mark)
		{
			$this->specialmark_model->deleteSpecialMarkProduct($mark->getId(), $product->getId());
			$this->specialmark_model->insertSpecialMarkProduct($mark->getId(), $product->getId());
		}
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE)
			return UPDATE_ERROR;
		
		return ERROR_NONE;
	}
	
	public function updateProductType($pids, $ptid)
	{
		$query = "UPDATE ".PRODUCTS_TABLE." SET product_type_id=".$ptid." WHERE id IN (";
		
		for ($i = 0, $size = count($pids); $i < $size - 1; $i++)
		{
			$query .= "?, ";
		}
		$query .= "?)";
		
		$this->db->trans_start();
		$this->db->query($query, $pids);
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE)
			return UPDATE_ERROR;
		return ERROR_NONE;
	}
	
	public function isProductExisted_id($id)
	{
		$this->db->where('id', $id);
		$data = $this->db->get(PRODUCTS_TABLE);

		return ($data->num_rows() > 0);
	}
	
	public function findProduct($name, $productTypeId)
	{
		$id = -1;
		
		$this->db->where('product_type_id', $productTypeId);
		$this->db->where('name', $this->db->escape_like_str($name));
		$data = $this->db->get(PRODUCTS_TABLE);
		
		if ($data->num_rows() > 0)
		{
			$row = $data->row();
			$id = $row->id;
		}
		
		return $id;
	}
}
?>